package com.pets.springboot_petshop.mapper;

import com.pets.springboot_petshop.entity.Petproduct;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @Author: wxk
 * @Email: 2089967794@qq.com
 * @Date: 2024/10/22 上午2:07 周二
 * @Description: 宠物活体商品数据访问层
 */
@Mapper
@Repository
public interface PetproductMapper {
    //id查询
    @Select("SELECT " +
            "a.id, " +
            "a.pettype_id, " +
            "a.name, " +
            "a.img, " +
            "a.age," +
            "a.sex, " +
            "a.description, " +
            "a.status, " +
            "c.name as pettype_name, " + // 为 pettype 表的 name 字段添加别名
            "a.create_time " +
            "FROM petproduct a " +
            "JOIN pettype c ON a.pettype_id = c.id " +
            "WHERE a.id = #{id} AND a.status = '未售出'")
    Petproduct select(int id);
    //id查询
    @Select("SELECT " +
            "a.id, " +
            "a.pettype_id, " +
            "a.name, " +
            "a.img, " +
            "a.age," +
            "a.sex, " +
            "a.description, " +
            "a.status, " +
            "a.user_id, " +
            "b.username ," +
            "c.name as pettype_name, " + // 为 pettype 表的 name 字段添加别名
            "a.create_time " +
            "FROM petproduct a " +
            "JOIN user b ON a.user_id = b.id " +
            "JOIN pettype c ON a.pettype_id = c.id " +
            "WHERE a.id = #{id} AND a.status = '已售出'")
    Petproduct selectuser(int id);
    //user_id查询
    @Select("SELECT " +
            "a.id, " +
            "a.pettype_id, " +
            "a.name, " +
            "a.img, " +
            "a.age," +
            "a.sex, " +
            "a.description, " +
            "a.status, " +
            "a.user_id, " +
            "b.username ," +
            "c.name as pettype_name, " + // 为 pettype 表的 name 字段添加别名
            "a.create_time " +
            "FROM petproduct a " +
            "JOIN user b ON a.user_id = b.id " +
            "JOIN pettype c ON a.pettype_id = c.id " +
            "WHERE a.user_id = #{id}")
    List<Petproduct> selectuser_id(int user_id);
    //pettype_id查询
    @Select("SELECT " +
            "a.id, " +
            "a.pettype_id, " +
            "a.name, " +
            "a.img, " +
            "a.age," +
            "a.sex, " +
            "a.description, " +
            "a.status, " +
            "a.user_id, " +
            "b.username," +
            "c.name as pettype_name, " + // 为 pettype 表的 name 字段添加别名
            "a.create_time " +
            "FROM petproduct a " +
            "JOIN user b ON a.user_id = b.id " +
            "JOIN pettype c ON a.pettype_id = c.id " +
            "WHERE a.pettype_id = #{id}")
    List<Petproduct> selectpettype_id(int pettype_id);
    //姓名模糊查询
    @Select("SELECT " +
            "a.id, " +
            "a.pettype_id, " +
            "a.name, " +
            "a.img, " +
            "a.age," +
            "a.sex, " +
            "a.description, " +
            "a.status, " +
            "a.user_id, " +
            "b.username ," +
            "c.name as pettype_name, " + // 为 pettype 表的 name 字段添加别名
            "a.create_time " +
            "FROM petproduct a " +
            "JOIN user b ON a.user_id = b.id " +
            "JOIN pettype c ON a.pettype_id = c.id " +
            "WHERE a.name LIKE CONCAT('%', #{name}, '%');")
    List<Petproduct> selectname(String name);
    //查询所有
    @Select("SELECT " +
            "a.id, " +
            "a.pettype_id, " +
            "a.name, " +
            "a.img, " +
            "a.age," +
            "a.sex, " +
            "a.description, " +
            "a.status, " +
            "a.user_id, " +
            "b.username, " +
            "c.name as pettype_name, " + // 为 pettype 表的 name 字段添加别名
            "a.create_time " +
            "FROM petproduct a " +
            "JOIN user b ON a.user_id = b.id " +
            "JOIN pettype c ON a.pettype_id = c.id;")
    List<Petproduct> findAll();
    //添加
    @Insert("insert into petproduct(pettype_id,name,img,age,sex,description,status,create_time,update_time)" +
            "values(#{pettype_id},#{name},#{img},#{age},#{sex},#{description},#{status},now(),now());")
    int add(Petproduct petproduct);
    //删除
    @Delete("delete from petproduct where id=#{id};")
    int delete(int id);
    //修改数据
    @Update("UPDATE petproduct SET pettype_id=#{pettype_id},name=#{name}," +
            "img=#{img},age=#{age},sex=#{sex},description=#{description},status=#{status},update_time=now() where id= #{id};")
    int update(Petproduct petproduct);
    //修改数据
    @Update("UPDATE petproduct SET pettype_id=#{pettype_id},name=#{name}," +
            "img=#{img},age=#{age},sex=#{sex},description=#{description},status=#{status},user_id=#{user_id},update_time=now() where id= #{id};")
    int updateuser_id(Petproduct petproduct);
}
